package com.examsys.dao;

import java.sql.Date;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.examsys.po.UserGroups;
import com.examsys.po.Users;

/**
 * 会员或者考试者数据访问层实现类
 * @author edu-1
 *
 */
public class UsersDaoImpl extends AbstractBaseDao<Users, Integer> implements UsersDao {

	/**
	 * 添加会员或者考试者
	 */
	@Override
	public void add(Users obj) throws Exception {
		//构造插入语句
		String sql="Insert into USERS (ID,GROUP_ID,USER_NAME,USER_PASS,USER_NO,REAL_NAME,EMAIL,PHONE,CREATE_DATE,LOGIN_DATE,LOGIN_TIMES,STATUS,REMARK) values (USERS_ID_SEQ.nextval,?,?,?,?,?,?,?,?,?,?,?,?)";
		this.execute(sql, new Object[]{obj.getUserGroups().getId(),obj.getUser_name(),obj.getUser_pass(),obj.getUser_no(),obj.getReal_name(),obj.getEmail(),obj.getPhone(),
				obj.getCreate_date(),obj.getLogin_date(),obj.getLogin_times(),obj.getStatus(),obj.getRemark()});
	}

	/**
	 * 更新会员或者考试者
	 */
	@Override
	public void update(Users obj) throws Exception {
		//构造更新语句
		String sql="UPDATE USERS SET GROUP_ID=?,USER_NAME=?,USER_PASS=?,USER_NO=?,REAL_NAME=?,EMAIL=?,PHONE=?,CREATE_DATE=?,LOGIN_DATE=?,LOGIN_TIMES=?,STATUS=?,REMARK=? WHERE ID=?";
		this.execute(sql, new Object[]{obj.getUserGroups().getId(),obj.getUser_name(),obj.getUser_pass(),obj.getUser_no(),obj.getReal_name(),obj.getEmail(),obj.getPhone(),
				obj.getCreate_date(),obj.getLogin_date(),obj.getLogin_times(),obj.getStatus(),obj.getRemark(),obj.getId()});
	}

	/**
	 * 删除会员或者考试者
	 */
	@Override
	public void delete(Integer id) throws Exception {
		//构建删除语句
		String sql="DELETE FROM USERS WHERE ID=?";
		this.execute(sql, new Object[]{id});
	}

	/**
	 * 获取会员或者考试者
	 * @param id 编号
	 */
	@Override
	public Users get(Integer id) throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.GROUP_ID,a.USER_NAME,a.USER_PASS,a.USER_NO,a.REAL_NAME,a.EMAIL,a.PHONE,a.CREATE_DATE,a.LOGIN_DATE,a.LOGIN_TIMES,a.STATUS,a.REMARK,b.GROUP_NAME FROM USERS a LEFT JOIN USER_GROUPS b ON a.GROUP_ID=b.ID WHERE a.ID=?";
		Map m = this.uniqueQuery(sql, new Object[]{id});//调用父类的查询方法拿数据
		
		Integer idd = (Integer)m.get("ID");//编号
		String user_name = (String)m.get("USER_NAME");//会员名
		String user_pass = (String)m.get("USER_PASS");//密码
		String user_no = (String)m.get("USER_NO");//学号
		String real_name = (String)m.get("REAL_NAME");//真实姓名
		String email = (String)m.get("EMAIL");//邮箱
		String phone = (String)m.get("PHONE");//手机号码
		Integer login_times = (Integer)m.get("LOGIN_TIMES");//登录次数
		String status = (String)m.get("STATUS");//状态
		String remark = (String)m.get("REMARK");//备注
		
		//由于数据返回的日期类型是Timestamp, 得强转
		Timestamp create_date = (Timestamp)m.get("CREATE_DATE");//注册时间
		Date create_date2 = new Date(create_date.getTime());//需要转换一下时间
		
		//由于数据返回的日期类型是Timestamp, 得强转
		Timestamp login_date = (Timestamp)m.get("LOGIN_DATE");//最后登录日期
		Date login_date2 = new Date(login_date.getTime());//需要转换一下时间
				
		Users users=new Users();//创建实体类对象
		users.setId(idd);
		users.setUser_name(user_name);
		users.setUser_pass(user_pass);
		users.setUser_no(user_no);
		users.setReal_name(real_name);
		users.setEmail(email);
		users.setPhone(phone);
		users.setLogin_times(login_times);
		users.setStatus(status);
		users.setRemark(remark);
		users.setLogin_date(login_date2);
		users.setCreate_date(create_date2);
		
		Integer group_id = (Integer)m.get("GROUP_ID");//编号
		String group_name = (String)m.get("GROUP_NAME");//会员组名称
		
		UserGroups userGroups=new UserGroups();//创建实体类对象
		userGroups.setId(group_id);
		userGroups.setGroup_name(group_name);
		
		users.setUserGroups(userGroups);//设置关联对象
		
		return users;
	}

	/**
	 * 获取所有会员或者考试者
	 */
	@Override
	public List<Users> getList() throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.GROUP_ID,a.USER_NAME,a.USER_PASS,a.USER_NO,a.REAL_NAME,a.EMAIL,a.PHONE,a.CREATE_DATE,a.LOGIN_DATE,a.LOGIN_TIMES,a.STATUS,a.REMARK,b.GROUP_NAME FROM USERS a LEFT JOIN USER_GROUPS b ON a.GROUP_ID=b.ID";
		List<Map> resultList = this.query(sql, new Object[]{});//调用父类的查询方法拿数据
		List<Users> list=new ArrayList<Users>();//存放Users类型对象的集合
		
		for(Map m:resultList){
			Integer idd = (Integer)m.get("ID");//编号
			String user_name = (String)m.get("USER_NAME");//会员名
			String user_pass = (String)m.get("USER_PASS");//密码
			String user_no = (String)m.get("USER_NO");//学号
			String real_name = (String)m.get("REAL_NAME");//真实姓名
			String email = (String)m.get("EMAIL");//邮箱
			String phone = (String)m.get("PHONE");//手机号码
			Integer login_times = (Integer)m.get("LOGIN_TIMES");//登录次数
			String status = (String)m.get("STATUS");//状态
			String remark = (String)m.get("REMARK");//备注
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp create_date = (Timestamp)m.get("CREATE_DATE");//注册时间
			Date create_date2 = new Date(create_date.getTime());//需要转换一下时间
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp login_date = (Timestamp)m.get("LOGIN_DATE");//最后登录日期
			Date login_date2 = new Date(login_date.getTime());//需要转换一下时间
					
			Users users=new Users();//创建实体类对象
			users.setId(idd);
			users.setUser_name(user_name);
			users.setUser_pass(user_pass);
			users.setUser_no(user_no);
			users.setReal_name(real_name);
			users.setEmail(email);
			users.setPhone(phone);
			users.setLogin_times(login_times);
			users.setStatus(status);
			users.setRemark(remark);
			users.setLogin_date(login_date2);
			users.setCreate_date(create_date2);
			
			Integer group_id = (Integer)m.get("GROUP_ID");//编号
			String group_name = (String)m.get("GROUP_NAME");//会员组名称
			
			UserGroups userGroups=new UserGroups();//创建实体类对象
			userGroups.setId(group_id);
			userGroups.setGroup_name(group_name);
			
			users.setUserGroups(userGroups);//设置关联对象
			list.add(users);
		}
		return list;
	}

	/**
	 * 带条件获取会员或者考试者
	 */
	@Override
	public List<Users> getList(Users obj) throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.GROUP_ID,a.USER_NAME,a.USER_PASS,a.USER_NO,a.REAL_NAME,a.EMAIL,a.PHONE,a.CREATE_DATE,a.LOGIN_DATE,a.LOGIN_TIMES,a.STATUS,a.REMARK,b.GROUP_NAME FROM USERS a LEFT JOIN USER_GROUPS b ON a.GROUP_ID=b.ID WHERE 1=1";
		if(obj!=null){//条件构造
			
			if(obj.getId()!=null&&obj.getId()!=0){
				sql+=" AND a.ID="+obj.getId();
			}
			
			if(obj.getUser_name()!=null&&!"".equals(obj.getUser_name())){
				sql+=" AND a.USER_NAME LIKE '%"+obj.getUser_name()+"%'";
			}
			
			if(obj.getUser_no()!=null&&!"".equals(obj.getUser_no())){
				sql+=" AND a.USER_NO='"+obj.getUser_no()+"'";
			}
			
			if(obj.getReal_name()!=null&&!"".equals(obj.getReal_name())){
				sql+=" AND a.REAL_NAME='"+obj.getReal_name()+"'";
			}
			
			if(obj.getUserGroups()!=null&&obj.getUserGroups().getId()!=null&&obj.getUserGroups().getId()!=0){
				sql+=" AND a.GROUP_ID="+obj.getUserGroups().getId();
			}
		}

		List<Map> resultList = this.query(sql, new Object[]{});//调用父类的查询方法拿数据
		List<Users> list=new ArrayList<Users>();//存放Users类型对象的集合
		
		for(Map m:resultList){
			Integer idd = (Integer)m.get("ID");//编号
			String user_name = (String)m.get("USER_NAME");//会员名
			String user_pass = (String)m.get("USER_PASS");//密码
			String user_no = (String)m.get("USER_NO");//学号
			String real_name = (String)m.get("REAL_NAME");//真实姓名
			String email = (String)m.get("EMAIL");//邮箱
			String phone = (String)m.get("PHONE");//手机号码
			Integer login_times = (Integer)m.get("LOGIN_TIMES");//登录次数
			String status = (String)m.get("STATUS");//状态
			String remark = (String)m.get("REMARK");//备注
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp create_date = (Timestamp)m.get("CREATE_DATE");//注册时间
			Date create_date2 = new Date(create_date.getTime());//需要转换一下时间
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp login_date = (Timestamp)m.get("LOGIN_DATE");//最后登录日期
			Date login_date2 = new Date(login_date.getTime());//需要转换一下时间
					
			Users users=new Users();//创建实体类对象
			users.setId(idd);
			users.setUser_name(user_name);
			users.setUser_pass(user_pass);
			users.setUser_no(user_no);
			users.setReal_name(real_name);
			users.setEmail(email);
			users.setPhone(phone);
			users.setLogin_times(login_times);
			users.setStatus(status);
			users.setRemark(remark);
			users.setLogin_date(login_date2);
			users.setCreate_date(create_date2);
			
			Integer group_id = (Integer)m.get("GROUP_ID");//编号
			String group_name = (String)m.get("GROUP_NAME");//会员组名称
			
			UserGroups userGroups=new UserGroups();//创建实体类对象
			userGroups.setId(group_id);
			userGroups.setGroup_name(group_name);
			
			users.setUserGroups(userGroups);//设置关联对象
			list.add(users);
		}
		return list;
	}
	
	
	/**
	 * 通过用户名去获取考试者
	 * @param userName
	 * @return
	 */
	public Users getUsersByUserName(String userName)throws Exception{
		//构建查询语句
		String sql="SELECT a.ID,a.GROUP_ID,a.USER_NAME,a.USER_PASS,a.USER_NO,a.REAL_NAME,a.EMAIL,a.PHONE,a.CREATE_DATE,a.LOGIN_DATE,a.LOGIN_TIMES,a.STATUS,a.REMARK,b.GROUP_NAME FROM USERS a LEFT JOIN USER_GROUPS b ON a.GROUP_ID=b.ID WHERE a.USER_NAME=?";
		Map m = this.uniqueQuery(sql, new Object[]{userName});//调用父类的查询方法拿数据
		
		Integer idd = (Integer)m.get("ID");//编号
		String user_name = (String)m.get("USER_NAME");//会员名
		String user_pass = (String)m.get("USER_PASS");//密码
		String user_no = (String)m.get("USER_NO");//学号
		String real_name = (String)m.get("REAL_NAME");//真实姓名
		String email = (String)m.get("EMAIL");//邮箱
		String phone = (String)m.get("PHONE");//手机号码
		Integer login_times = (Integer)m.get("LOGIN_TIMES");//登录次数
		String status = (String)m.get("STATUS");//状态
		String remark = (String)m.get("REMARK");//备注
		
		//由于数据返回的日期类型是Timestamp, 得强转
		Timestamp create_date = (Timestamp)m.get("CREATE_DATE");//注册时间
		Date create_date2 = new Date(create_date.getTime());//需要转换一下时间
		
		//由于数据返回的日期类型是Timestamp, 得强转
		Timestamp login_date = (Timestamp)m.get("LOGIN_DATE");//最后登录日期
		Date login_date2 = new Date(login_date.getTime());//需要转换一下时间
				
		Users users=new Users();//创建实体类对象
		users.setId(idd);
		users.setUser_name(user_name);
		users.setUser_pass(user_pass);
		users.setUser_no(user_no);
		users.setReal_name(real_name);
		users.setEmail(email);
		users.setPhone(phone);
		users.setLogin_times(login_times);
		users.setStatus(status);
		users.setRemark(remark);
		users.setLogin_date(login_date2);
		users.setCreate_date(create_date2);
		
		Integer group_id = (Integer)m.get("GROUP_ID");//编号
		String group_name = (String)m.get("GROUP_NAME");//会员组名称
		
		UserGroups userGroups=new UserGroups();//创建实体类对象
		userGroups.setId(group_id);
		userGroups.setGroup_name(group_name);
		
		users.setUserGroups(userGroups);//设置关联对象
		
		return users;	
	}

}
